package cn.edu.seu.cose.qol.model.dao.database.account;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cn.edu.seu.cose.qol.model.dao.database.QolSchemaConnectionFactory;
import cn.edu.seu.cose.qol.model.po.account.UserAccount;


public class UserAccountDataAccess {

	private static UserAccountDataAccess _instance;
	
	private static final String COLUMN_USER_ACCOUNT_ID = "user_account_id";
	
	private static final String COLUMN_USERNAME = "username";
	
	private static final String COLUMN_PASSWORD = "password";

	private static final String STATEMENT_RETRIEVE_USER_ACCOUNT_BY_ID =
			"SELECT * FROM qol_user_account WHERE user_account_id=?;";

	private static final String STATEMENT_RETRIEVE_USER_ACCOUNT_BY_USERNAME =
			"SELECT * FROM qol_user_account WHERE username=?;";
	
	private static final String STATEMENT_CREATE_USER_ACCOUNT =
			"INSERT INTO qol_user_account (username, password) " +
			"VALUES (?, PASSWORD(?));";
	
	private static final String STATEMENT_DELETE_USER_ACCOUNT_BY_ID =
			"DELETE FROM qol_user_account WHERE user_account_id=?;";
	
	private static final String STATEMENT_DELETE_USER_ACCOUNT_BY_USERNAME =
			"DELETE FROM qol_user_account WHERE username=?;";
	
	private static final String STATEMENT_UPDATE_USER_ACCOUNT =
			"UPDATE qol_user_account " +
			"SET username=?, password=PASSWORD(?) WHERE user_account_id=?;";
	
	private static final String STATEMENT_UPDATE_USER_ACCOUNT_USERNAME =
			"UPDATE qol_user_account SET username=? WHERE user_account_id=?;";
	
	private static final String STATEMENT_UPDATE_USER_ACCOUNT_PASSWORD =
			"UPDATE qol_user_account SET password=PASSWORD(?) " +
			"WHERE user_account_id=?;";
	
	private QolSchemaConnectionFactory _factory;
	
	public static UserAccountDataAccess getInstance() {
		if (_instance == null) {
			_instance = new UserAccountDataAccess();
		}
		
		return _instance;
	}
	
	protected UserAccountDataAccess() {
		_factory = QolSchemaConnectionFactory.getInstance();
	}

	public UserAccount getUserAccountById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_USER_ACCOUNT_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			long userAccountId = rs.getLong(COLUMN_USER_ACCOUNT_ID);
			String username = rs.getString(COLUMN_USERNAME);
			String password = rs.getString(COLUMN_PASSWORD);
			UserAccount userAccount = new UserAccount();
			userAccount.setUserAccountId(userAccountId);
			userAccount.setUsername(username);
			userAccount.setPassword(password);
	
			connection.close();
			return userAccount;
		} finally {
			connection.close();
		}
	}
	
	public UserAccount getUserAccountByUsername(String name)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_RETRIEVE_USER_ACCOUNT_BY_USERNAME);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			
			if (!rs.next()) {
				return null;
			}
			
			long userAccountId = rs.getLong(COLUMN_USER_ACCOUNT_ID);
			String username = rs.getString(COLUMN_USERNAME);
			String password = rs.getString(COLUMN_PASSWORD);
			UserAccount userAccount = new UserAccount();
			userAccount.setUserAccountId(userAccountId);
			userAccount.setUsername(username);
			userAccount.setPassword(password);
	
			connection.close();
			return userAccount;
		} finally {
			connection.close();
		}
	}
	
	public long createNewUserAccount(UserAccount userAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_CREATE_USER_ACCOUNT,
					Statement.RETURN_GENERATED_KEYS);
			String username = userAccount.getUsername();
			String password = userAccount.getPassword();
			ps.setString(1, username);
			ps.setString(2, password);
			ps.executeUpdate();
			
			ResultSet rs = ps.getGeneratedKeys();
			if (!rs.next()) {
				return -1;
			}
	
			connection.close();
			return rs.getLong(1);
		} finally {
			connection.close();
		}
	}
	
	public void deleteUserAccountById(long id) throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(
					STATEMENT_DELETE_USER_ACCOUNT_BY_ID);
			ps.setLong(1, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}
	
	public void deleteUserAccountByUsername(String username)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_DELETE_USER_ACCOUNT_BY_USERNAME);
			ps.setString(1, username);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}
	
	public void updateUserAccount(UserAccount userAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_USER_ACCOUNT);
			long id = userAccount.getUserAccountId();
			String username = userAccount.getUsername();
			String password = userAccount.getPassword();
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setLong(3, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}
	
	public void updateUserAccountUsername(UserAccount userAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_USER_ACCOUNT_USERNAME);
			long id = userAccount.getUserAccountId();
			String username = userAccount.getUsername();
			ps.setString(1, username);
			ps.setLong(2, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}
	
	public void updateUserAccountPassword(UserAccount userAccount)
			throws SQLException {
		Connection connection = _factory.newConnection();
		try {
			PreparedStatement ps =
					connection.prepareStatement(
							STATEMENT_UPDATE_USER_ACCOUNT_PASSWORD);
			long id = userAccount.getUserAccountId();
			String password = userAccount.getPassword();
			ps.setString(1, password);
			ps.setLong(2, id);
			ps.executeUpdate();
			connection.close();
		} finally {
			connection.close();
		}
	}

}
